package com.soujiayi.store;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.soujiayi.model.SkimHistory;
import com.soujiayi.model.TuanCity;

import com.soujiayi.model.Favorite;

import com.soujiayi.util.Constants;
import com.soujiayi.weibo.User;

public class DatabaseManager {

	private DatabaseHelper databaseHelper;
	private SQLiteDatabase db;

	private final static String SEARCH_SKIM_HISTORY_SQL = "select * from "
			+ Constants.SKIM_HISTORY_TABLE_NAME + " order by timestamp desc";

	public DatabaseManager(Context context) {
		databaseHelper = new DatabaseHelper(context, null, 28);
	}

	public void insertUser(User user) {
		deleteUser(String.valueOf(user.getUid()));
		db = databaseHelper.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("type", user.getType());
		values.put("uid", String.valueOf(user.getUid()));
		values.put("username", user.getUsername());
		values.put("nickname", user.getNickname());
		values.put("email", user.getEmail());
		values.put("password", user.getPassword());
		values.put("cityid", user.getCityId());
		values.put("cityname", user.getCityName());
		values.put("gender", user.getGender());
		values.put("birthday", user.getBirthday());
		values.put("avatar_url", user.getAvatar_url());
		values.put("status", Constants.USER_STATUS_ONLINE);

		db.insert(Constants.USERINFO_TABLE_NAME, null, values);
		db.close();
	}
	
	public void updateUserFields(int uid, String avatar_url, String nickName , String gender, String cityId, String cityName,String birthday) {
		db = databaseHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		if(avatar_url!= null && !"".equals(avatar_url))
			values.put("avatar_url", avatar_url);
		if(nickName!= null && !"".equals(nickName))
			values.put("nickname", nickName);
		if(gender!= null && !"".equals(gender))
			values.put("gender", gender);
		if(cityId!= null && !"".equals(cityId))
			values.put("cityid", cityId);
		if(cityName!= null && !"".equals(cityName))
			values.put("cityname", cityName);
		if(birthday!= null && !"".equals(birthday))
			values.put("birthday", birthday);
		db.update(Constants.USERINFO_TABLE_NAME, values, "uid=?", new String[]{String.valueOf(uid)});
		db.close();
	}

	public void deleteUser(String uid) {
		db = databaseHelper.getWritableDatabase();
		db.delete(Constants.USERINFO_TABLE_NAME, "uid=?", new String[] { uid });
		db.close();
	}

	public void insertShareUser(User user) {
		db = databaseHelper.getWritableDatabase();

		db.delete(Constants.SHARE_TABLE_NAME, "type=?",
				new String[] { String.valueOf(user.getType()) });

		ContentValues values = new ContentValues();

		values.put("type", user.getType());
		if (user.getType() == Constants.USER_TYPE_TENCENT) {
			values.put("tencent_token", user.getTencent_token());
			values.put("tencent_expires_in", user.getTencent_expires_in());
			values.put("tencent_expires_time", user.getTencent_expires_time());
			values.put("tencent_clientId", user.getTencent_clientId());
			values.put("tencent_clientIp", user.getTencent_clientIp());
			values.put("tencent_clientSecret", user.getTencent_clientSecret());
			values.put("tencent_oauthVersion", user.getTencent_oauthVersion());
			values.put("tencent_openid", user.getTencent_openid());
			values.put("tencent_openkey", user.getTencent_openkey());
			values.put("tencent_redirectUri", user.getTencent_redirectUri());
			values.put("tencent_scope", user.getTencent_scope());
		} else if (user.getType() == Constants.USER_TYPE_SINA) {
			values.put("sina_token", user.getSina_token());
			values.put("sina_expires_in", user.getSina_expires_in());
			values.put("sina_expires_time", user.getSina_expires_time());
		}

		db.insert(Constants.SHARE_TABLE_NAME, null, values);
		db.close();
	}

	public void deleteShareUser(int type) {
		db = databaseHelper.getWritableDatabase();
		db.delete(Constants.SHARE_TABLE_NAME, "type=?",
				new String[] { String.valueOf(type) });
		db.close();
	}

	public List<User> getShareUser() {
		List<User> users = new ArrayList<User>();
		db = databaseHelper.getReadableDatabase();
		String[] columns = { "_id", "type", "uid", "sina_token",
				"sina_expires_in", "sina_expires_time", "tencent_token",
				"tencent_expires_in", "tencent_expires_time",
				"tencent_clientId", "tencent_clientIp", "tencent_clientSecret",
				"tencent_oauthVersion", "tencent_openid", "tencent_openkey",
				"tencent_redirectUri", "tencent_scope" };
		Cursor cursor = db.query(Constants.SHARE_TABLE_NAME, columns, null,
				null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			User user = new User();
			int type = cursor.getInt(cursor.getColumnIndex("type"));
			if (type == Constants.USER_TYPE_TENCENT) {
				String tencent_token = cursor.getString(cursor
						.getColumnIndex("tencent_token"));
				String tencent_expires_in = cursor.getString(cursor
						.getColumnIndex("tencent_expires_in"));
				String tencent_expires_time = cursor.getString(cursor
						.getColumnIndex("tencent_expires_time"));
				String tencent_clientId = cursor.getString(cursor
						.getColumnIndex("tencent_clientId"));
				String tencent_clientIp = cursor.getString(cursor
						.getColumnIndex("tencent_clientIp"));
				String tencent_clientSecret = cursor.getString(cursor
						.getColumnIndex("tencent_clientSecret"));
				String tencent_oauthVersion = cursor.getString(cursor
						.getColumnIndex("tencent_oauthVersion"));
				String tencent_openid = cursor.getString(cursor
						.getColumnIndex("tencent_openid"));
				String tencent_openkey = cursor.getString(cursor
						.getColumnIndex("tencent_openkey"));
				String tencent_redirectUri = cursor.getString(cursor
						.getColumnIndex("tencent_redirectUri"));
				String tencent_scope = cursor.getString(cursor
						.getColumnIndex("tencent_scope"));
				user.setTencent_token(tencent_token);
				user.setTencent_expires_in(tencent_expires_in);
				user.setTencent_expires_time(tencent_expires_time);
				user.setTencent_clientId(tencent_clientId);
				user.setTencent_clientIp(tencent_clientIp);
				user.setTencent_clientSecret(tencent_clientSecret);
				user.setTencent_oauthVersion(tencent_oauthVersion);
				user.setTencent_openid(tencent_openid);
				user.setTencent_openkey(tencent_openkey);
				user.setTencent_redirectUri(tencent_redirectUri);
				user.setTencent_scope(tencent_scope);
			} else if (type == Constants.USER_TYPE_SINA) {
				String sina_token = cursor.getString(cursor
						.getColumnIndex("sina_token"));
				String sina_expires_in = cursor.getString(cursor
						.getColumnIndex("sina_expires_in"));
				String sina_expires_time = cursor.getString(cursor
						.getColumnIndex("sina_expires_time"));
				user.setSina_token(sina_token);
				user.setSina_expires_in(sina_expires_in);
				user.setSina_expires_time(sina_expires_time);
			}

			users.add(user);
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		return users;
	}

	public void userLogin(User user) {
		db = databaseHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("status", Constants.USER_STATUS_ONLINE);
		db.update(Constants.USERINFO_TABLE_NAME, values, "uid=?",
				new String[] { String.valueOf(user.getUid()) });
		db.close();
	}

	public void userLogout(User user) {
		db = databaseHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("status", Constants.USER_STATUS_OFFLINE);
		db.update(Constants.USERINFO_TABLE_NAME, values, "uid=?",
				new String[] { String.valueOf(user.getUid()) });
		db.close();
	}

	public boolean isUserExist(User user) {
		db = databaseHelper.getReadableDatabase();
		boolean isExist = false;
		if (user.getType() == Constants.USER_TYPE_SOUJIAYI) {
			Cursor cursor = db.query(Constants.USERINFO_TABLE_NAME,
					new String[] { "_id" }, "username=?",
					new String[] { user.getUsername() }, null, null, null);
			cursor.moveToFirst();
			if (!cursor.isAfterLast()) {
				isExist = true;
			}
			cursor.close();
		}
		db.close();
		return isExist;
	}

	public User getOnlineUser() {
		User user = null;
		db = databaseHelper.getReadableDatabase();
		String[] columns = { "_id", "type", "uid", "username","nickname", "email", "password",
				"cityid","cityname", "gender","birthday", "avatar_url", "status" };
		Cursor cursor = db.query(Constants.USERINFO_TABLE_NAME, columns,
				"status=?", new String[] { "1" }, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			user = new User();
			int type = cursor.getInt(cursor.getColumnIndex("type"));
			String uid = cursor.getString(cursor.getColumnIndex("uid"));
			String username = cursor.getString(cursor.getColumnIndex("username"));
			String nickname = cursor.getString(cursor.getColumnIndex("nickname"));
			String email = cursor.getString(cursor.getColumnIndex("email"));
			String password = cursor.getString(cursor
					.getColumnIndex("password"));
			String cityId = cursor.getString(cursor.getColumnIndex("cityid"));
			String cityName = cursor.getString(cursor.getColumnIndex("cityname"));
			String gender = cursor.getString(cursor.getColumnIndex("gender"));
			String birthday = cursor.getString(cursor.getColumnIndex("birthday"));
			String avatar_url = cursor.getString(cursor.getColumnIndex("avatar_url"));
			int status = cursor.getInt(cursor.getColumnIndex("status"));

			user.setType(type);
			user.setUid(Integer.parseInt(uid));
			user.setUsername(username);
			user.setNickname(nickname);
			user.setEmail(email);
			user.setPassword(password);
			user.setCityId(cityId);
			user.setCityName(cityName);
			user.setGender(gender);
			user.setBirthday(birthday);
			user.setAvatar_url(avatar_url);
			user.setStatus(status);

			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		return user;
	}
	
	public void insertSearchHistory(String key, String type) {
		deleteSearchHistory(key, type);
		if (getCount(Constants.SEARCHKEYHISTORY_TABLE_NAME, "where type='"
				+ type + "'") == 10) {
			db = databaseHelper.getWritableDatabase();
			db.execSQL("delete from " + Constants.SEARCHKEYHISTORY_TABLE_NAME
					+ " where _id=(select min(_id) from "
					+ Constants.SEARCHKEYHISTORY_TABLE_NAME + " where type='"
					+ type + "')");
			db.close();
		}
		db = databaseHelper.getWritableDatabase();
		db.execSQL("insert into " + Constants.SEARCHKEYHISTORY_TABLE_NAME
				+ "(searchkey,type) values('" + key + "','" + type + "')");
		db.close();
	}

	public void deleteSearchHistory(String key, String type) {
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		db.delete(Constants.SEARCHKEYHISTORY_TABLE_NAME,
				"searchkey=? and type=?", new String[] { key, type });
		db.close();
	}

	public int getCount(String table) {
		int count = 0;
		db = databaseHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from " + table, null);

		if (cursor.moveToNext()) {
			count = cursor.getInt(0);
		}
		cursor.close();
		db.close();
		return count;
	}

	public int getCount(String table, String where) {
		int count = 0;
		db = databaseHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from " + table + " "
				+ where, null);

		if (cursor.moveToNext()) {
			count = cursor.getInt(0);
		}
		cursor.close();
		db.close();
		return count;
	}

	public ArrayList<HashMap<String, Object>> getSearchHistory(String type) {
		ArrayList<HashMap<String, Object>> history = new ArrayList<HashMap<String, Object>>();
		db = databaseHelper.getReadableDatabase();
		String SEARCH_KEYHISTORY_SQL = "select * from "
				+ Constants.SEARCHKEYHISTORY_TABLE_NAME + " where type='"
				+ type + "' order by timestamp desc";

		Cursor cursor = db.rawQuery(SEARCH_KEYHISTORY_SQL, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			String searchkey = cursor.getString(cursor
					.getColumnIndex("searchkey"));
			HashMap<String, Object> map = new HashMap<String, Object>();
			map.put(Constants.SEARCH_LIST_ITEM, searchkey);
			history.add(map);
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		return history;
	}

	public void deleteSearchHistory(String type) {
		db = databaseHelper.getWritableDatabase();
		db.delete(Constants.SEARCHKEYHISTORY_TABLE_NAME, "type=?",
				new String[] { type });
		db.close();
	}

	public void insertCities(ArrayList<TuanCity> list) {
		db = databaseHelper.getWritableDatabase();
		db.execSQL("delete from " + Constants.CITIES_TABLE_NAME);
		db.beginTransaction();
		for (int i = 0; i < list.size(); i++) {
			ContentValues values = new ContentValues();
			values.put("city_id", list.get(i).getCityid());
			values.put("name", list.get(i).getName());
			values.put("firstchar", list.get(i).getFirstchar());
			values.put("pinyin", list.get(i).getPinyin());
			values.put("orders", list.get(i).getOrder());
			db.insert(Constants.CITIES_TABLE_NAME, null, values);
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		db.close();
	}

	public ArrayList<LinkedHashMap<String, Object>> getAreaCities() {
		ArrayList<LinkedHashMap<String, Object>> listMap = new ArrayList<LinkedHashMap<String, Object>>();
		db = databaseHelper.getReadableDatabase();
		Cursor cursor = null;
		for (char i = 'A'; i <= 'Z'; i++) {
			cursor = db.query(Constants.CITIES_TABLE_NAME,
					new String[] { "name" }, "firstchar=? and orders=?",
					new String[] { String.valueOf(i), "0" }, null, null,
					"orders desc, pinyin asc");
			cursor.moveToFirst();
			ArrayList<Object> areaCities = new ArrayList<Object>();
			while (!cursor.isAfterLast()) {
				String name = cursor.getString(0);
				areaCities.add(name);
				cursor.moveToNext();
			}
			if (areaCities.size() != 0) {
				LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
				map.put(Constants.TUAN_CITY_AREA, String.valueOf(i));
				map.put(Constants.TUAN_CITY_CITIES, areaCities);
				listMap.add(map);
			}
		}
		cursor.close();
		db.close();
		return listMap;
	}

	public ArrayList<String> getHotCities() {
		db = databaseHelper.getReadableDatabase();
		ArrayList<String> hotCities = new ArrayList<String>();
		Cursor cursor = db.query(Constants.CITIES_TABLE_NAME,
				new String[] { "name" }, "orders<>?", new String[] { "0" },
				null, null, "orders desc, pinyin asc");
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			String name = cursor.getString(0);
			hotCities.add(name);
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		return hotCities;
	}

	public String getCityIdByName(String name) {
		String cityId = "";
		db = databaseHelper.getReadableDatabase();
		Cursor cursor = db.query(Constants.CITIES_TABLE_NAME,
				new String[] { "city_id" }, "name=?", new String[] { name },
				null, null, null);
		if (cursor.moveToFirst())
			cityId = cursor.getString(0);
		db.close();
		return cityId;
	}

	public String getCityNameById(String city_id) {
		String name = "";
		db = databaseHelper.getReadableDatabase();
		Cursor cursor = db.query(Constants.CITIES_TABLE_NAME,
				new String[] { "name" }, "city_id=?", new String[] { city_id },
				null, null, null);
		if (cursor.moveToFirst())
			name = cursor.getString(0);
		db.close();
		return name;
	}

	public ArrayList<String> getFavorites(String type) {
		ArrayList<String> favList = new ArrayList<String>();
		db = databaseHelper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("type", type);
		Cursor cursor =  db.query(Constants.FAVORITE_TABLE_NAME,new String[] { "goodsid" }, "type=?", new String[]{type},null,null,null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			String goodsid = cursor.getString(0);
			favList.add(goodsid);
			cursor.moveToNext();
		};
		db.close();
		return favList;
	}
	
	public void insertFavorite(String type, String autoid, String favid) {
		db = databaseHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("type", type);
		if(type.equals("goods"))	
			values.put("goodsid", autoid);
		else
			values.put("mallid", autoid);
		values.put("favid", favid);
		db.insert(Constants.FAVORITE_TABLE_NAME, null, values);
		db.close();
	}
	
	public void insertFavorites(List<Map<String,String>> list,String type) {
		db = databaseHelper.getWritableDatabase();
		for (int i =0; i <list.size() ;i++){
			ContentValues values = new ContentValues();
			values.put("type", type);
			if(type.equals("goods"))				
				values.put("goodsid", list.get(i).get("goodsid"));
			else if(type.equals("mall"))	
				values.put("mallid", list.get(i).get("mallid"));
			values.put("favid", list.get(i).get("favid"));
			db.insert(Constants.FAVORITE_TABLE_NAME, null, values);
		}
		db.close();
	}
	
	public void deleteAllFavorites(String type) {
		db = databaseHelper.getWritableDatabase();
		db.delete(Constants.FAVORITE_TABLE_NAME,"type=?",new String[]{type});
		db.close();
	}
	
	public void deleteFavorite(String type, String autoid) {
		db = databaseHelper.getWritableDatabase();
		if(type.equals("goods"))
			db.delete(Constants.FAVORITE_TABLE_NAME, "type=? and goodsid=?",
				new String[] {type, autoid });
		else if (type.equals("mall"))
			db.delete(Constants.FAVORITE_TABLE_NAME, "type=? and mallid=?",
				new String[] {type, autoid });
		db.close();
	}

	public boolean isFavoriteExist(String type, String autoid) {
		int count = 0;
		if(type.equals("goods"))
			count = getCount(Constants.FAVORITE_TABLE_NAME, "where type='"+type+"' and goodsid='"+autoid+"'");
		else if (type.equals("mall"))
			count = getCount(Constants.FAVORITE_TABLE_NAME, "where type='"+type+"' and mallid='"+autoid+"'");
		return count > 0 ? true : false;
	}

	public void insertSKimHistory(SkimHistory skimHistory) {
		db = databaseHelper.getWritableDatabase();
		db.delete(Constants.SKIM_HISTORY_TABLE_NAME, "goodsid=?",
				new String[] { skimHistory.getGoodsid() });
		db.close();

		if (getCount(Constants.SKIM_HISTORY_TABLE_NAME, "where type="
				+ skimHistory.getType()) >= 20) {
			db = databaseHelper.getWritableDatabase();
			db.execSQL("delete from " + Constants.SKIM_HISTORY_TABLE_NAME
					+ " where _id=(select min(_id) from "
					+ Constants.SKIM_HISTORY_TABLE_NAME + " where type="
					+ skimHistory.getType() + ")");
			db.close();
		}

		db = databaseHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("type", skimHistory.getType());
		values.put("goodsid", skimHistory.getGoodsid());
		values.put("name", skimHistory.getName());
		values.put("cover", skimHistory.getCover());
		values.put("saleprice", skimHistory.getSaleprice());
		values.put("mallid", skimHistory.getMallid());
		values.put("mallname", skimHistory.getMallname());
		values.put("mallCount", skimHistory.getMallCount());
		db.insert(Constants.SKIM_HISTORY_TABLE_NAME, null, values);
		db.close();
	}

	public ArrayList<SkimHistory> getSkimHistory() {
		ArrayList<SkimHistory> skimHistoryList = new ArrayList<SkimHistory>();
		db = databaseHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(SEARCH_SKIM_HISTORY_SQL, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			SkimHistory skimHistory = new SkimHistory();
			int type = cursor.getInt(cursor.getColumnIndex("type"));
			String goodsid = cursor.getString(cursor.getColumnIndex("goodsid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String cover = cursor.getString(cursor.getColumnIndex("cover"));
			String saleprice = cursor.getString(cursor
					.getColumnIndex("saleprice"));
			String mallid = cursor.getString(cursor.getColumnIndex("mallid"));
			String mallname = cursor.getString(cursor
					.getColumnIndex("mallname"));
			String mallCount = cursor.getString(cursor
					.getColumnIndex("mallCount"));
			skimHistory.setType(type);
			skimHistory.setGoodsid(goodsid);
			skimHistory.setName(name);
			skimHistory.setCover(cover);
			skimHistory.setSaleprice(saleprice);
			skimHistory.setMallid(mallid);
			skimHistory.setMallname(mallname);
			skimHistory.setMallCount(mallCount);
			skimHistoryList.add(skimHistory);
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		return skimHistoryList;
	}

	public void deleteSkimHistory() {
		db = databaseHelper.getWritableDatabase();
		db.delete(Constants.SKIM_HISTORY_TABLE_NAME, null, null);
		db.close();
	}

	public void closeDB() {
		if (db != null && db.isOpen()) {

			db.close();
		}
	}

}
